import os
import streamlit as st
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
from datetime import datetime
from itertools import chain
from besins_selenium_downloaders.file_manager import *
from besins_selenium_downloaders.streamlit_tools import (
    configure_page_navigation,
)


class XlsxViewer:
    def __init__(self):
        self.setup_page_config()
        configure_page_navigation("xlsx_viewer")
        self.main()

    def setup_page_config(self):
        st.set_page_config(
            page_title="Excel Viewer",
            page_icon="📊",
            layout="wide",
            initial_sidebar_state="expanded",
        )

        # Load and inject common CSS
        common_css_path = os.path.join(os.path.dirname(__file__), "../css/common.css")
        with open(common_css_path) as f:
            st.markdown(f"<style>{f.read()}</style>", unsafe_allow_html=True)

        css_path = os.path.join(os.path.dirname(__file__), "../css/xlsx_viewer.css")
        with open(css_path) as f:
            st.markdown(f"<style>{f.read()}</style>", unsafe_allow_html=True)

    def main(self):
        st.markdown(
            '<h1 class="common-title">📊 XLSX Viewer</h1>', unsafe_allow_html=True
        )

        # Instructions
        st.markdown(
            """
            Select one or more XLSX files from different date folders to view or combine the data.
            Choose your preferred viewer type for an interactive experience.
            """
        )

        # Step 1: Select Date Folders
        date_folders = get_list_date_folders()
        if not date_folders:
            st.error("No date folders found in the current directory.")
            return

        # Extract query parameters
        query_params = st.query_params
        query_passed_selected_date = query_params.get("date", None)
        query_passed_selected_site = query_params.get("site", None)

        date_folders.sort(reverse=True)
        
        # Set default folder based on URL parameter
        if query_passed_selected_date in date_folders:
            default_folders = [query_passed_selected_date]
        else:
            default_folders = [date_folders[0]] if date_folders else []

        selected_folders = st.multiselect(
            "📅 Select Date Folders",
            options=date_folders,
            default=default_folders,
            help="Choose one or more date folders containing XLSX files.",
        )
        
        if not selected_folders:
            st.warning("Please select at least one date folder to proceed.")
            return

        # Step 2: Select XLSX Files
        xlsx_files = self.get_xlsx_files(selected_folders)
        if not xlsx_files:
            st.error("No XLSX files found in the selected folders.")
            return
        
        # Pre-select XLSX files based on the selected site
        if query_passed_selected_site and query_passed_selected_date:
            preselected_files = [
                f for f in xlsx_files 
                if f"{query_passed_selected_date}/{query_passed_selected_site}" in f
            ]
        else:
            preselected_files = []


        selected_files = st.multiselect(
            "📄 Select XLSX Files",
            options=xlsx_files,
            default=preselected_files,
            help="Choose one or more XLSX files to view or combine.",
        )
        if not selected_files:
            st.warning("Please select at least one XLSX file to proceed.")
            return

        # Step 3: Choose Operation
        operation = st.selectbox(
            "🔍 Choose Operation",
            options=["👁️ View Individually", "🔗 Combine Dataframes"],
            help="Select how you want to handle the selected XLSX files.",
        )

        # Step 4: Execute Operation
        if operation == "👁️ View Individually":
            self.view_individual(selected_files)
        elif operation == "🔗 Combine Dataframes":
            self.combine_dataframes(selected_files)

    def get_xlsx_files(self, selected_folders):
        """Retrieve all XLSX files from the selected folders."""
        xlsx_files = []
        for folder in selected_folders:
            folder_path = os.path.join(os.getcwd(), folder)
            for file in os.listdir(folder_path):
                if file.endswith(".xlsx"):
                    xlsx_files.append(os.path.join(folder, file))
        return xlsx_files

    def load_dataframe(self, file_path):
        """Load an XLSX file into a pandas DataFrame."""
        try:
            df = pd.read_excel(file_path)
            st.success(f"✅ Loaded {os.path.basename(file_path)} successfully!")
            return df
        except Exception as e:
            st.error(f"❌ Error loading {os.path.basename(file_path)}: {str(e)}")
            return None

    def view_individual(self, selected_files):
        """View each selected XLSX file individually."""
        for file in selected_files:
            st.markdown(f"### 📄 {os.path.basename(file)}")
            df = self.load_dataframe(os.path.join(os.getcwd(), file))
            if df is not None:
                # Display the DataFrame
                self.display_dataframe(df, key_prefix=f"view_{os.path.basename(file)}")
                

    def combine_dataframes(self, selected_files):
        """Combine multiple DataFrames into one."""
        dfs = []
        for file in selected_files:
            df = self.load_dataframe(os.path.join(os.getcwd(), file))
            if df is not None:
                df['Source_File'] = os.path.basename(file)
                dfs.append(df)

        if not dfs:
            st.error("No valid DataFrames to combine.")
            return

        combined_df = pd.concat(dfs, ignore_index=True)
        st.markdown("### 🔄 Combined DataFrames")
        
        # Display the combined DataFrame
        self.display_dataframe(combined_df, key_prefix="combined")
        

    def display_dataframe(self, df, key_prefix=""):
        """Display the DataFrame using Streamlit Data Editor."""
        # Add filters for columns
        with st.expander("🔍 Filter Data", expanded=False):
            # Date range filter if datetime columns exist
            date_cols = df.select_dtypes(include=['datetime64']).columns
            if not date_cols.empty:
                selected_date_col = st.selectbox(
                    "Select Date Column",
                    options=date_cols,
                    key=f"{key_prefix}_date_col"
                )
                start_date, end_date = st.date_input(
                    "Select Date Range",
                    value=(df[selected_date_col].min(), df[selected_date_col].max()),
                    key=f"{key_prefix}_date_range"
                )
                df = df[
                    (df[selected_date_col].dt.date >= start_date) &
                    (df[selected_date_col].dt.date <= end_date)
                ]

            # Numeric range filters
            num_cols = df.select_dtypes(include=['float64', 'int64']).columns
            if not num_cols.empty:
                selected_num_col = st.selectbox(
                    "Select Numeric Column to Filter",
                    options=num_cols,
                    key=f"{key_prefix}_num_col"
                )
                
                # Get min and max values for the selected column
                min_value = float(df[selected_num_col].min())
                max_value = float(df[selected_num_col].max())
                
                # Handle case where min and max are the same
                if min_value == max_value:
                    st.info(f"All values in {selected_num_col} are {min_value}")
                    min_val = max_val = min_value
                else:
                    min_val, max_val = st.slider(
                        f"Filter range for {selected_num_col}",
                        min_value=min_value,
                        max_value=max_value,
                        value=(min_value, max_value),
                        key=f"{key_prefix}_num_range"
                    )
                    df = df[
                        (df[selected_num_col] >= min_val) &
                        (df[selected_num_col] <= max_val)
                    ]

            # Text search filter
            text_cols = df.select_dtypes(include=['object']).columns
            if not text_cols.empty:
                selected_text_col = st.selectbox(
                    "Select Text Column to Filter",
                    options=text_cols,
                    key=f"{key_prefix}_text_col"
                )
                search_term = st.text_input(
                    f"Search in {selected_text_col}",
                    key=f"{key_prefix}_search"
                )
                if search_term:
                    df = df[df[selected_text_col].str.contains(search_term, case=False, na=False)]

        # Sort options
        sort_col = st.selectbox(
            "Sort by Column",
            options=df.columns,
            key=f"{key_prefix}_sort_col"
        )
        sort_ascending = st.checkbox(
            "Sort Ascending",
            value=True,
            key=f"{key_prefix}_sort_order"
        )
        df = df.sort_values(by=sort_col, ascending=sort_ascending)

        # Display the filtered and sorted DataFrame
        st.data_editor(
            df,
            use_container_width=True,
            num_rows="dynamic",
            key=f"{key_prefix}_editor"
        )


if __name__ == "__main__":
    XlsxViewer()
